MySQL Q&A

如何探知MySQL是否可用？

• MySQL不可用的情况：

1、MySQL 没启动
2、MySQL 连接数太多未响应

• 通过Shell脚本检测mysqld是否可用

创建表的同时写入数据

• 使用场景：根据已存在的数据表来创建表并把已知表中某字段数据写入

CREATE TABLE tb_goods_brand 
（
    brand_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENY,
    brand_name VERCHAR(40) NOT NULL
）
SELECT brand_name FROM tb_goods GROUD BY brand_name

插入数据

• 根据已有表的数据进行插入（提取已有表的数据）

INSERT INTO tb1(goods_cate) SELECT cate_name FROM tb2 GROUD BY cate_name

• 单表插入

###指定表的字段添加###
INSERT INTO tb1(id,name) VALUES(1,'小米6')

###按照表的字段顺序添加### （不推荐）
INSERT INTO tb1 VALUES(1,'小米6')

• 单表插入多条记录

###指定表的字段添加多条###
INSERT INTO tb1(id,name) VALUES(1,'小米6'),(2,'华为')

###按照表的字段顺序添加多条### （不推荐）
INSERT INTO tb1 VALUES(1,'小米6'),(2,'华为')

更新数据

• 根据已有表的数据进行更新（提取已有表的数据）

UPDATE tb1 JOIN tb2 ON tb1.goods_cate = tb2.cate_name SET tb1.goods_cate = tb2.id

• 单表更新(一次更新多个字段)

UPDATE tb1 SET name = '你好', age = 18 WHERE id > 18

##列自更新##
UPDATE tb1 SET age = age + 18 WHERE id > 18

##不加where条件会全更新

• 同时更新多张表

##方式1##
UPDATE at,bt SET at.c1=1,bt.c2=2 WHERE at.id=bt.id;

##方式2##
UPDATE at JOIN bt ON at.id=bt.id SET at.c1=1,bt.c2=2;

• 把表中2个字段的值交换

## 错误写法 ##
update product set original_price=price,price=original_price;

## 正确写法 ##
update product as a, product as b 
set a.original_price=b.price, a.price=b.original_price 
where a.id=b.id;

删除数据

• 单表删除

##删除指定数据##
DELETE FROM student WHERE id > 5;

##删除所有数据##
DELETE FROM student;

• 多表删除

##使用到子查询##
DELETE n,na FROM news AS n 
JOIN (select * FROM news GROUP BY title HAVING count(*)>=2) AS st ON n.title = st.title
JOIN news_attr AS na ON n.id = na.news_id 
WHERE n.id <> st.id

• 注意事项

1、如果只想删除某些记录中某个字段的值，请使用UPDATE语句
2、想删除表中字段，使用ALTER
3、DELETE是整条记录删除，只想删除某个字段的数据，请使用UPDATE

GROUP BY 分组 过滤与统计

• 单字段

只要此字段的值相同，就归为一类，并且只显示表中按录入顺序最先的一条记录，其余的过滤掉

• 多字段

只要多字段的值都相同，就归为一类，只要有一个字段不同，都不能归为一类，显示与上面一样

select * from tb group by col1,col2;

• 作用

1、过滤 ———— 分组后相当于过滤，但这个作用并非主要作用

---

2、统计

##没用分组，进行统计##
select AVG(score) from tb //所有的分数的平均值（一条记录）

##使用分组，进行统计，会按分组统计##
select AVG(score) from tb group by sid //所有的分数的平均值（多条记录）

HAVING 用法

• 注意事项

1、必须配合group by分组一起使用，没有group by就不能使用having
2、having后面跟的字段必须在select的字段中出现或使用聚合函数

• HAVING与WHERE的区别

相同点：HAVING和WHERE都是过滤
唯一区别：WHERE过滤行，HAVING过滤组

ORDER BY 排序用法

• 单字段排序

select * from tb order by id desc   //升序asc  降序desc  

• 多字段排序

##
多字段排序必须按照字段书写顺序来执行，如果第一个字段排序后，
该字段没有出现同值的记录，那后面字段将不再排序
##

##
如果该字段有相同的值的话，同值的记录再根据第二个字段排序
##

select * from tb order by id desc,age desc

• 特殊用法

##如果你想把某些数据放在最开头或最后尾，可以这样写：##
select * from tb order by id in(4,2,6) desc,age desc   //开头
select * from tb order by id in(4,2,6),age desc    //结尾

LIMIT 限制返回记录数

select * from tb limit 2   ##返回2条记录

select * from tb limit 5,2     ##从第6条记录开始返回2条记录 （第一条记录位置是0）

LIKE 模糊查询及注意事项

• 通配符

## like语句中的通配符：%、_和escape

## 其中%做为通配符通配多个，_作为通配符通配一个, escape转义使用

select * from user where username like '%huxiao';   
select * from user where username like 'huxiao%';   
select * from user where username like '%hu%xiao%';   

select * from user where username like '_huxia';   //匹配Ahuxia
select * from user where username like 'huxia_';   //匹配huxiaA
select * from user where username like 'h_xiao';   //匹配huxiao

## 如果要查询的字段中含有 % 或 _ 时候，需要使他们不再作为通配符，就必须使用escape
select * from user where username like '/_nihao%' escape '/';    //匹配_nihaoAAAA
##意思就是说/之后的_不作为通配符

• 效率问题

## 当LIKE查询的字段加了索引之后，查询时某种写法会出现效率问题

select * from user where username like '%huxiao';   ## %放在前面，查询很慢 
select * from user where username like 'huxiao%';   ## 查询很快
select * from user where username like 'hu%xiao%';  ## 查询很快

MySQL连接JOIN的用法

• SELECT语句，支持JOIN操作

SELECT * FROM tb1 JOIN tb2 ON tb1.id = tb2.id

• 更新语句，支持JOIN操作

UPDATE tb1 JOIN tb2 ON tb1.id = tb2.id SET tb1.name = 'news' WHERE tb1.id > 5;

• 删除语句，支持JOIN操作

DELETE a,b FROM ta AS a JOIN tb AS b ON a.id = b.id WHERE a.id>5;

• 注意事项

##内连接，获取的是2张表的交集部分
##外连接，获取数据>=主表数据

MySQL内置函数

• 字符连接函数

## CONCAT() 连接字符

SELECT CONCAT(firstName,lastName) AS fullName FROM student

## CONCAT_WS() 使用哪种分隔符进行字符连接

SELECT CONCAT('-',firstName,lastName) AS fullName FROM student

## CONCAT()与CONCAT_WS() 两个函数返回的结果都是字符型

• 数字格式函数 （使用千分位格式，四舍五入）

## FORMAT() 数字格式化

SELECT FORMAT(5201314.75,1)          //'5201314.8'
SELECT FORMAT('5201314.74','1')          //'5201314.7'

## FORMAT() 函数返回的是字符型数据

• 字符长度与去掉字符空格相关函数

## LENGTH() 获取字符长度

SELECT FORMAT('wutao') 

## TRIM()/LTRIM()/RTRIM() 去掉字符前后空格

• 聚合函数

## 聚合函数的共同点：只返回一个值

## 聚合函数分别有：MAX()/MIN()/AVG()/SUM()/COUNT() 最大值/最小值/平均值/求和/数量

• 信息函数

## VERSION() 获取MySQL版本号
SELECT VERSION();

## USER() 获取当前用户信息
SELECT USER();

## CONNECTION_ID() 连接ID
SELECT CONNECTION_ID();

## LAST_INNERT_ID() 最后插入ID
SELECT LAST_INNERT_ID(); //如果同时插入2条数据，只会取第一条记录ID

UNION 与 UNION ALL 用法

• 用法

##合并（联合）

##将两个或者多个结果集（select）合并

• 注意

##在使用时，两个结果集要有相同的列，并且字段类型需要一致。

select id,name from tableA
union all
select name from tableB

• 区别

union 会去除结果集中重复的部分，相当于进行一个distinct（去重）；

union all 会不管是否重复，都会将结果合并在一起输出。

SELECT语句 执行顺序

书写顺序：
select ... from... where... group by... having... order by... limit ...

执行顺序：
from... on... join... where... group by... having.... select ... order by... limit

存储过程

• 优势

1、减少网络流量
2、增强SQL的功能和灵活性（相当于程序了）
3、执行速度更加快

• 传参的模式与类型

## 模式
IN : 调用存储过程必须传过来，但不能返回
OUT
INOUT

• 不带参数的存储过程
• 带参数的存储过程
• 如何调用存储过程

## 调用不带参数的存储过程
CALL fn()

## 调用带参数的存储过程
CALL fn(3,12)

会话变量

两种定义方式：
1、set @num := 'wutao';
2、select @num := 'wutao';